package org.inspursc.s0517.health.evaluation.service.impl;

import lombok.extern.slf4j.Slf4j;
import org.hibernate.Session;
import org.hibernate.query.NativeQuery;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.inspursc.s0517.health.evaluation.dto.HealthResultSectionCountDTO;
import org.inspursc.s0517.health.evaluation.dto.OrganizationCountDTO;
import org.inspursc.s0517.health.evaluation.dto.TwoLevelCategoryDTO;
import org.inspursc.s0517.health.evaluation.service.StatisticsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.transaction.Transactional;
import java.util.List;

/**
 * @author yuchu
 */
@Service
@Slf4j
public class StatisticsServiceImpl implements StatisticsService {

    @Autowired
    @PersistenceContext
    private EntityManager entityManager;


    /**
     * 机构统计服务
     * @return 统计结果
     */
    @Override
    @Transactional(rollbackOn = Exception.class)
    public List<OrganizationCountDTO> getOrganizationCount() {
        //final String sql="select o.name as organizationName,count(u.organization_id) as totalCount from sys_user u left join sys_organization o on u.organization_id=o.id  group by o.name";
        final String sql="select o.name as organizationName,count(u.organization_id) as totalCount from sys_organization o left join t_customer u  on u.organization_id=o.id  group by o.id";
        Session session=entityManager.unwrap(Session.class);
        NativeQuery query=session.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(OrganizationCountDTO.class));
        entityManager.close();
        List<OrganizationCountDTO> organizationCountDTOS=query.getResultList();
        return organizationCountDTOS;
    }
    /**
     * 单机构所服务客户统计
     * @return 统计结果
     */
    @Override
    @Transactional(rollbackOn = Exception.class)
    public  List<OrganizationCountDTO> getOneOrganizationCount(Integer orgId) {
        final String sql="select o.name as organizationName,count(u.organization_id) as totalCount from t_customer u left join sys_organization o on u.organization_id=o.id where u.organization_id =" + orgId + " GROUP by o.id";
        Session session=entityManager.unwrap(Session.class);
        NativeQuery query=session.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(OrganizationCountDTO.class));
        entityManager.close();
        List<OrganizationCountDTO> organizationCountDTOS=query.getResultList();
        return organizationCountDTOS;
    }

    /**
     * 获取健康报告的区间分值统计
     * @return
     */
    @Override
    @Transactional(rollbackOn = Exception.class)
    public List<HealthResultSectionCountDTO> getHeathResultCount() {

        final String sql="SELECT elt( INTERVAL ( total_score, 0, 55, 70, 85 ), '小于55', '55-70','70-85', '85以上') as 'level',count( id ) as 'total' FROM t_health_result \n" +
                "GROUP BY level";
        Session session=entityManager.unwrap(Session.class);
        NativeQuery query=session.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(HealthResultSectionCountDTO.class));
        entityManager.close();
        List<HealthResultSectionCountDTO> healthResultSectionCountDTOS=query.getResultList();
        return healthResultSectionCountDTOS;
    }
    @Override
    @Transactional(rollbackOn = Exception.class)
    public List<HealthResultSectionCountDTO> getHeathResultCountByOrg(Integer orgId) {
        final String sql="SELECT elt( INTERVAL ( total_score, 0, 55, 70, 85 ), '小于55', '55-70','70-85', '85以上') as 'level',count( id ) as 'total' FROM t_health_result \n" +
                "WHERE organization_id = "+ orgId +" GROUP BY level";
        Session session=entityManager.unwrap(Session.class);
        NativeQuery query=session.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(HealthResultSectionCountDTO.class));
        entityManager.close();
        List<HealthResultSectionCountDTO> healthResultSectionCountDTOS=query.getResultList();
        return healthResultSectionCountDTOS;
    }


    /**
     * 获取2级维度的平均分,既往病史评估不在统计内
     * @return
     */
    @Override
    @Transactional(rollbackOn = Exception.class)
    public List<TwoLevelCategoryDTO> getTwoLevelCategoryAvg() {
        final String sql="SELECT cat.NAME AS cateName,avg( b.total ) AS avgScore FROM t_question_category cat" +
                " LEFT JOIN ( SELECT health_result_id, second_category_id, SUM( option_score ) as total FROM t_health_record GROUP BY health_result_id, second_category_id ) b ON cat.id = b.second_category_id" +
                " WHERE parent_id != 0 And cat.name !='既往病史评估'" +
                " GROUP BY cat.NAME";
        Session session=entityManager.unwrap(Session.class);
        NativeQuery query=session.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(TwoLevelCategoryDTO.class));
        entityManager.close();
        List<TwoLevelCategoryDTO> twoLevelCategoryDTOS=query.getResultList();
        return twoLevelCategoryDTOS;
    }

    @Override
    @Transactional(rollbackOn = Exception.class)
    public List<TwoLevelCategoryDTO> getTwoLevelCategoryAvgByOrg(Integer orgId) {
        final String sql="SELECT cat.NAME AS cateName,avg( b.total ) AS avgScore FROM t_question_category cat" +
                " LEFT JOIN ( SELECT health_result_id, second_category_id, SUM( option_score ) as total FROM t_health_record WHERE organization_id = "+ orgId +"  GROUP BY health_result_id, second_category_id ) b ON cat.id = b.second_category_id" +
                " WHERE parent_id != 0 And cat.name !='既往病史评估'" +
                " GROUP BY cat.NAME";
        Session session=entityManager.unwrap(Session.class);
        NativeQuery query=session.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(TwoLevelCategoryDTO.class));
        entityManager.close();
        List<TwoLevelCategoryDTO> twoLevelCategoryDTOS=query.getResultList();
        return twoLevelCategoryDTOS;
    }
}
